package com.thinkingstar.iads.datacenter.entity.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;

import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.common.utils.SwapUtil;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignCell;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignConstant;

/**
 * @ClassName: DaoDcProjectDesignContent 
 * @Description:数据中心_项目_设计单元维护
 * @author 1157372219@qq.com 
 * @date 2018年3月19日   
 */
@IocBean(args = {"refer:dao"})
public class DaoDcProjectDesignCell extends EntityRepositoryServiceImpl<DcProjectDesignCell>{

	public DaoDcProjectDesignCell(Dao dao) {
		super(dao);
	}
	
	
	public DcProjectDesignCell findDesignCellByName(String phase_id , String content_id , String major_id){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false).and("design_cell_table_name", "like", "%房屋%")
		.and("project_design_phase_id", "=", phase_id).and("project_design_content_id", "=", content_id)
		.and("project_design_major_id", "=", major_id);
		List <DcProjectDesignCell> list = query(criteria);
		return !list.isEmpty()?list.get(0):null;
	}

	/** 获取段长编组车数汇总数据*/
	public NutMap findDcbzcsCountByDesignCellName(String project_id,String phase_id , String content_id , String design_cell_name){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false)
				.and("project_design_phase_id", "=", phase_id).and("project_design_content_id", "=", content_id)
				.and("design_cell_table_name", "like", "%"+design_cell_name+"%");
		List <DcProjectDesignCell> list = query(criteria);
		DcProjectDesignCell designCell = null;
		if(list.size()>0){
			designCell = list.get(0);
		}
		if (null!= designCell) {
			NutMap nutMap = new NutMap();
			StringBuffer stringBuffer = new StringBuffer("select sum(cbzyycsjq) cbzyycsjq,sum(cbzyycsyq) cbzyycsyq ,sum(dbzyycsjq) dbzyycsjq," +
					"sum(dbzyycsyq) dbzyycsyq ,sum(cbzpscsjq) cbzpscsjq,sum(cbzpscsyq)cbzpscsyq " +
					",sum(dbzpscsjq) dbzpscsjq,sum(dbzpscsyq) dbzpscsyq from dc_designcell_"+designCell.getDesign_cell_table_code()+" where project_id='"+project_id+"' ");
			Sql sqls = Sqls.create(stringBuffer.toString());
			sqls.setCallback(new SqlCallback() {
				List<NutMap> list = new ArrayList<NutMap>();
				@Override
				public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
					while (rs.next()) {
						NutMap nutMap = new NutMap();
						nutMap.put("cbzyycsjq", rs.getString("cbzyycsjq"));
						nutMap.put("cbzyycsyq", rs.getString("cbzyycsyq"));
						nutMap.put("dbzyycsjq", rs.getString("dbzyycsjq"));
						nutMap.put("dbzyycsyq", rs.getString("dbzyycsyq"));
						nutMap.put("cbzpscsjq", rs.getString("cbzpscsjq"));
						nutMap.put("cbzpscsyq", rs.getString("cbzpscsyq"));
						nutMap.put("dbzpscsjq", rs.getString("dbzpscsjq"));
						nutMap.put("dbzpscsyq", rs.getString("dbzpscsyq"));
						list.add(nutMap);
					}
					return list;
				}
			});
			dao().execute(sqls);
			List<NutMap> result = sqls.getList(NutMap.class);
			if (result.size()>0) {
				return result.get(0);
			} else {
				return null;
			}
		 } else {
			return null;
		}
	}
	//查询所有设计单元表单项
	public QueryResult findDesignCellList(NutMap nutMap){
		List<Map> page_list = null;
		Pager pager = null;
		String sqlStr = "select ddc.* , ddp.project_design_phase_name , dpdc.project_design_content_name , dpdm.project_design_major_name  "
				+ " from dc_project_design_cell ddc "
				+ " left join dc_project_design_phase ddp on ddc.project_design_phase_id = ddp.id "
				+ " left join dc_project_design_content dpdc on ddc.project_design_content_id = dpdc.id"
				+ " left join dc_project_design_major dpdm on ddc.project_design_major_id = dpdm.id"
				+ " where ddc.del_flag = '0' and ddp.del_flag = '0' and dpdc.del_flag = '0' and dpdm.del_flag = '0' ";
		if (nutMap.get("design_cell_table_name")!=null) {
			sqlStr += " and ddc.design_cell_table_name like @design_cell_table_name ";
		}
		StringBuffer stringBuffer = new StringBuffer(sqlStr
				+ " order by ddc.orderBy+0 ");
		Sql sqls = Sqls.create(stringBuffer.toString());
		if (nutMap.get("design_cell_table_name")!=null) {
			sqls.params().set("design_cell_table_name","%"+nutMap.get("design_cell_table_name")+"%");
		}
		if(nutMap.get("page_num") != null && nutMap.get("page_size") != null){
			pager = new Pager(nutMap.getInt("page_num"), nutMap.getInt("page_size"));
			pager.setRecordCount(this.findDesignCellNum());
			sqls.setPager(pager);
		}
		sqls.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<Map<String,Object>> list = new ArrayList<>();
				while (rs.next()){
					Map<String,Object> map = new HashMap<>();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sqls);
		page_list = sqls.getList(Map.class);
		return new QueryResult(page_list,pager);
	}
	
	//查询所有记录数
	public int findDesignCellNum(){
		StringBuffer stringBuffer = new StringBuffer("select count(ddc.id) num "
				+ " from dc_project_design_cell ddc "
				+ " left join dc_project_design_phase ddp on ddc.project_design_phase_id = ddp.id "
				+ " left join dc_project_design_content dpdc on ddc.project_design_content_id = dpdc.id"
				+ " left join dc_project_design_major dpdm on ddc.project_design_major_id = dpdm.id"
				+ " where ddc.del_flag = '0' and ddp.del_flag = '0' and dpdc.del_flag = '0' and dpdm.del_flag = '0' ");
		Sql sqls = Sqls.create(stringBuffer.toString());
		sqls.setCallback(new SqlCallback() {
			int count = 0;
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next())
				count = rs.getInt("num");
				return count;
			}
		});
		dao().execute(sqls);
		return sqls.getInt();
	}

	//查询某设计内容匹配的相设计单元信息
	public List <DcProjectDesignCell> getDesignCellDataByContent(String content_strs , String design_phase_id){
		String fin_sql = "select * from dc_project_design_cell where project_design_phase_id = @design_phase_id " +
				"and project_design_content_id = @content_id and del_flag='0' and isShow='1' order by orderBy asc" ;
		Sql sql = Sqls.create(fin_sql);
		sql.params().set("design_phase_id", design_phase_id);
		sql.params().set("content_id", content_strs);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <DcProjectDesignCell> list = new LinkedList<>();
				while (rs.next()) {
					DcProjectDesignCell add_obj = new DcProjectDesignCell();
					add_obj.setId(rs.getString("id"));
					add_obj.setDesign_cell_table_code(rs.getString("design_cell_table_code"));
					add_obj.setDesign_cell_table_name(rs.getString("design_cell_table_name"));
					list.add(add_obj);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(DcProjectDesignCell.class);
	}
	
	public Object findDesignCellByContentIdAndPhaseId(NutMap param){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false);
		if(StringUtils.isNotBlank(param.getString("content_id"))){
			criteria.where().and("project_design_content_id", "=", param.get("content_id"));
		}
		if(StringUtils.isNotBlank(param.getString("major_id"))){
			criteria.where().and("project_design_major_id", "=", param.get("major_id"));
		}
		if(StringUtils.isNotBlank(param.getString("phase_id"))){
			criteria.where().and("project_design_phase_id", "=", param.get("phase_id"));
		}
		criteria.getOrderBy().asc("orderBy+0");
		List <DcProjectDesignCell> find_list = query(criteria);
		return find_list;
	}

	public List<NutMap> initSelect(String designcell_tablename,String field_name,String project_id) {
		List <NutMap> result_list = null;
		String find_sql = " select distinct("+field_name+") from "+designcell_tablename+" where project_id='"+project_id+"'";
		Sql sql = Sqls.create(find_sql);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList();
				while (rs.next()) {
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);
						map.put(filed, value);
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		result_list = sql.getList(NutMap.class);
		return result_list;
	}
}
